package com.conversationboard.standalone.checkdb;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import com.conversationboard.cache.newanswers.NewAnswersThread;
import com.conversationboard.database.Database;
import com.conversationboard.logger.Logger;
import com.conversationboard.model.User;

public class ConsistencyCheck {

	public static class Thread {

		private int boardId;
		private int threadId;
		private String title;


		public int getBoardId() {
			return boardId;
		}


		public void setBoardId(int boardId) {
			this.boardId = boardId;
		}


		public int getThreadId() {
			return threadId;
		}


		public void setThreadId(int threadId) {
			this.threadId = threadId;
		}


		public String getTitle() {
			return title;
		}


		public void setTitle(String title) {
			this.title = title;
		}


		public Thread(int boardId, int threadId, String title) {
			this.boardId = boardId;
			this.threadId = threadId;
			this.title = title;
		}

	}


	public static void consistencyCheck(boolean fix, User user) throws Throwable {

		List<Thread> threads = new LinkedList<Thread>();
		List<NewAnswersThread> fixThreads = new LinkedList<NewAnswersThread>();

		Connection connection = Database.getConnection();

		PreparedStatement statement = connection.prepareStatement("SELECT boardid, threadid, title from threads");
		statement.execute();

		ResultSet resultSet = statement.getResultSet();

		while (resultSet.next()) {
			int boardId = resultSet.getInt("boardid");
			int threadId = resultSet.getInt("threadid");
			String title = resultSet.getString("title");
			Thread thread = new Thread(boardId, threadId, title);
			threads.add(thread);
		}

		statement.close();

		PreparedStatement find = connection.prepareStatement("SELECT COUNT(*) as count FROM messages WHERE boardid=? and threadid=?");

		for (Thread t : threads) {

			find.setInt(1, t.getBoardId());
			find.setInt(2, t.getThreadId());

			find.execute();

			ResultSet r = find.getResultSet();
			int count = 1;

			while (r.next()) {
				count = r.getInt("count");
			}

			if (count == 0) {
				String message = t.getBoardId() + ":" + t.getThreadId() + "   " + t.getTitle();
				Logger.log(new Date(), message, user);

				NewAnswersThread thread = new NewAnswersThread(t.getBoardId(), t.getThreadId());
				fixThreads.add(thread);
			}
		}

		Logger.log(new Date(), fixThreads.size() + " broken threads", user);

		statement.close();

		if (fix) {
			PreparedStatement fixStatement = connection.prepareStatement("DELETE FROM threads WHERE boardid=? AND threadid=?");

			for (NewAnswersThread thread : fixThreads) {
				fixStatement.setInt(1, thread.getBoardId());
				fixStatement.setInt(2, thread.getThreadId());
				fixStatement.addBatch();
			}

			fixStatement.executeBatch();
			fixStatement.close();
		}

		connection.close();

	}
}
